
package DBLayer;
import java.sql.*;
import ModelLayer.StandardTask;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBStandardTask implements IFDBStandardTask
{
    private Connection con;
    private PreparedStatement pStmtSelect;
    
    /**
     * Constructor for class DBStandardTask
     */
    public DBStandardTask() 
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    /**
     * Method to get all StandardTasks from the database
     * @return returns an ArrayList with StandardTasks
     */
    @Override
    public ArrayList<StandardTask> getAllStandardTasks()
    {
        return findStandardTasksWithPreQuery(null, "");
    }
    
    /**
     * Method to find a task with a specific id
     * @param standardTaskId int standardTaskId the id of the StandardTask
     * @return returns a StandardTask Object 
     */
    @Override
    public StandardTask searchStandardTask(int standardTaskId)
    {
        String wClause = "  where standardTaskId = ";
        StandardTask stTaskObj = new StandardTask();
        stTaskObj = findWithPreQuery(standardTaskId, wClause);
        
        return stTaskObj;
    }
    
    /**
     * Method to insert a StandardTask into the database
     * @param stTask an Object of the type StandardTask
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int insertStandardTask(StandardTask stTask) 
    {
        //call to get the next standardTask id
        int nextStandardTaskId = getMaxId();
        nextStandardTaskId = nextStandardTaskId + 1;
        System.out.println("next StandardTaskId = " +  nextStandardTaskId);
        PreparedStatement pstmt = null;
        int rc = -1;
        
        String insertStandardTask = "insert into StandardTask(standardTaskId, name, description,standardDisclaimer)"+
                     "values (?,?,?,?)";
        System.out.println(insertStandardTask);
        try
        {
            pstmt = con.prepareStatement(insertStandardTask);
            pstmt.setInt(1,nextStandardTaskId);
            pstmt.setString(2, stTask.getName());
            pstmt.setString(3,stTask.getDescription());
            pstmt.setString(4, stTask.getStandardDisclaimer());
 
            rc = pstmt.executeUpdate();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Insert exception in Company db: "+e.getMessage());
        }
        
        return rc; 
    }
    
    /**
     * Method to update a standard task
     * @param stTask an Object of the type StandardTask
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int updateStandardTask(StandardTask stTask)
    {
        StandardTask stTaskObj  = stTask;
        int rc=-1;
        
        PreparedStatement pstmt = null;
        
        String query = "UPDATE StandardTask SET "+
                "name = ?, "+
                "description = ?, "+
                "standardDisclaimer = ? "+
                "WHERE standardTaskId = ? ";
                
        System.out.println("Update query:" + query);
        
        try
        {
            //Update StandardTask
            pstmt = con.prepareStatement(query);
            pstmt.setString(1, stTaskObj.getName());
            pstmt.setString(2, stTaskObj.getDescription());
            pstmt.setString(3, stTaskObj.getStandardDisclaimer());
            pstmt.setInt(4, stTaskObj.getStandardTaskId());
            rc = pstmt.executeUpdate();
            pstmt.close();
        }//end try
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Delete exception in StandardTask db: "+e.getMessage());
        }
        
        return rc;
    }
    
    /**
     * Method to delete a standard task
     * @param standardTaskId int standardTaskId the id of the StandardTask
     * @return returns an int 1 if the method succedes and -1 if the method fails
     */
    @Override
    public int deleteStandardTask(int standardTaskId)
    {
        int rc = -1;
     
        PreparedStatement pstmt = null;
        String query = "delete from StandardTask where standardTaskId = ?";
        System.out.println(query);
        try
        {
            pstmt = con.prepareStatement(query);
            pstmt.setInt(1,standardTaskId);
            rc = pstmt.executeUpdate(); 
        }
        
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error:"+sqlE.getMessage());
        }
        
        catch(Exception e)
        {
            System.out.println("Delete exception in StandardTask db: "+e.getMessage());
        }
        
        return rc;  
    }
    
    /**
     * Method to build an object of the type StandardTask
     * @param results
     * @return returns a StandardTask object
     */
    private StandardTask buildStandardTask(ResultSet results)
    {
        StandardTask stTaskObj = new StandardTask();
        try
        {
            stTaskObj.setStandardTaskId(results.getInt("standardTaskId"));
            stTaskObj.setName(results.getString("name"));
            stTaskObj.setDescription(results.getString("description"));
            stTaskObj.setStandardDisclaimer(results.getString("standardDisclaimer"));
        }
        catch(Exception e)
        {
            System.out.println("buildStandardTask exception in StandardTask db: "+e.getMessage());
        }
        return stTaskObj;
    }
    
    /**
     * Method to create a prepared statement 
     * @param wClause String wClause
     * @return returns a PreparedStatment
     */
    private PreparedStatement getPreparedStmt(String wClause)
    {
        String query = "";
        try
        {
            if(wClause.equals(""))
            {
                query = "Select * FROM StandardTask ";
            }
            else
            {
                query = "Select * FROM StandardTask " +
                            wClause + " ?";
            }
           
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
        }//end try
        catch(Exception e)
        {
            System.out.println("getPreparedStmt exception in StandardTask db: "+e.getMessage());
        }
        return pStmtSelect;
    }
    
    
    
    /**
     * Method that finds a StandardTask in the database 
     * @param standardTaskId int standardTaskId the id of the StandardTask
     * @param wClause String wClause
     * @return returns a StandardTask Object 
     */
    private StandardTask findWithPreQuery(int standardTaskId, String wClause)
    {
        System.out.println("findWithPreQuery");
        
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);
        }
        StandardTask stTaskObj = null;
        ResultSet results;

        try
        {
            pStmtSelect.setInt(1,standardTaskId);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                stTaskObj = buildStandardTask(results);
            }
        }
        
        catch(Exception e)
        {
            System.out.println("findWithPreQuery exception in StandardTask db: "+e.getMessage());
        }
       
        return stTaskObj;
    }
    
    /**
     * Method to get arraylist with all standardTasks
     * @param value Object value the value which is used to search, in this case an int id
     * @param wClause String wClause
     * @return returns standardTasks an arryList with StandardTasks
     */
    private ArrayList<StandardTask> findStandardTasksWithPreQuery(Object value, String wClause)
    {
        System.out.println("Find with preCompiled");
        int stdTaskId = 0;
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);          
        }
        ArrayList<StandardTask> standardTasks = new ArrayList<StandardTask>();
        ResultSet results;
        try
        {    
            if(!wClause.equals(""))
            {
                stdTaskId = (Integer) value;
                pStmtSelect.setInt(1,stdTaskId);
            }//end if
            
            results = pStmtSelect.executeQuery();
            while(results.next() )
            {
                StandardTask stdTaskObj = new StandardTask();
		stdTaskObj = buildStandardTask(results);	
                standardTasks.add(stdTaskObj);
            }//end while
        }//end try
        catch(Exception e)
        {
            System.out.println("findStandardTasksWithPreQuery exception in StandardTask db: "+e.getMessage());
        }
        
        return standardTasks;
    }
    
    /**
     * Method to get the highest standardTaskId from the database
     * @return returns an int which is the highest standardTaskId from the database
     */
    public int getMaxId()
    {
        int maxStandardTaskId = GetMax.getMaxId("Select max(standardTaskId) from StandardTask");
        return maxStandardTaskId;
    }
}